package com.ruoyi.backend3.comparator;

import com.ruoyi.backend3.domain.*;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

public class DbComparator {

  public CompareResult compare(Db db1, Db db2) {
    Map<String, List<Column>> db1Tables = fetchTableColumns(db1);
    Map<String, List<Column>> db2Tables = fetchTableColumns(db2);

    List<TableDiff> tableDiffs = new ArrayList<>();
    List<ColumnDiff> columnDiffs = new ArrayList<>();

    // Compare tables
    Set<String> allTables = new HashSet<>(db1Tables.keySet());
    allTables.addAll(db2Tables.keySet());

    for (String tableName : allTables) {
      TableDiff tableDiff = new TableDiff();
      tableDiff.setName(tableName);

      if (!db1Tables.containsKey(tableName)) {
        tableDiff.setDiff(4); // 表仅存在于 db2 中
      } else if (!db2Tables.containsKey(tableName)) {
        tableDiff.setDiff(3); // 表仅存在于 db1 中
      } else {
        tableDiff.setDiff(2); // 假设表格不同，除非另有证明

        List<Column> columnsDb1 = db1Tables.get(tableName);
        List<Column> columnsDb2 = db2Tables.get(tableName);

        // 比较列
        Set<String> allColumns = new HashSet<>(columnsDb1.stream().map(Column::getName).collect(Collectors.toList()));
        allColumns.addAll(columnsDb2.stream().map(Column::getName).collect(Collectors.toList()));

        for (String columnName : allColumns) {
          ColumnDiff columnDiff = new ColumnDiff();
          columnDiff.setTable(tableName);

          if (!columnsDb1.stream().anyMatch(c -> c.getName().equals(columnName))) {
            columnDiff.setDiff(4); // 列仅存在于 db2 中
          } else if (!columnsDb2.stream().anyMatch(c -> c.getName().equals(columnName))) {
            columnDiff.setDiff(3); // 该列仅存在于 db1 中
          } else {
            // 比较列属性
            Column columnFromDb1 = columnsDb1.stream().filter(c -> c.getName().equals(columnName)).findFirst().orElse(null);
            Column columnFromDb2 = columnsDb2.stream().filter(c -> c.getName().equals(columnName)).findFirst().orElse(null);

            if (columnFromDb1.equals(columnFromDb2)) {
              columnDiff.setDiff(1); // 列是相同的
            } else {
              columnDiff.setDiff(2); // 列不同
            }
          }

          columnDiffs.add(columnDiff);
        }

        if (columnDiffs.stream().allMatch(cd -> cd.getDiff() == 1)) {
          tableDiff.setDiff(1); // 所有列都相同
        }
      }

      tableDiffs.add(tableDiff);
    }

    return new CompareResult(columnDiffs, tableDiffs);
  }

  private Map<String, List<Column>> fetchTableColumns(Db db) {
    Map<String, List<Column>> tables = new HashMap<>();
    try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword())) {
      DatabaseMetaData metaData = conn.getMetaData();
      ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});

      while (rs.next()) {
        String tableName = rs.getString("TABLE_NAME");
        List<Column> columns = new ArrayList<>();
        ResultSet rsCols = metaData.getColumns(null, null, tableName, "%");

        while (rsCols.next()) {
          String columnName = rsCols.getString("COLUMN_NAME");
          String columnType = rsCols.getString("TYPE_NAME");
          boolean nullable = rsCols.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
          String defaultValue = rsCols.getString("COLUMN_DEF");
          columns.add(new Column(columnName, columnType, nullable, defaultValue));
        }

        tables.put(tableName, columns);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return tables;
  }

  public static void main(String[] args) {
    Db db1 = new Db();
    db1.setName("Database 1");
    db1.setUrl("jdbc:mysql://localhost:3306/db_qczl");
    db1.setUsername("root");
    db1.setPassword("123456");

    Db db2 = new Db();
    db2.setName("Database 2");
    db2.setUrl("jdbc:mysql://localhost:3306/db_cloud");
    db2.setUsername("root");
    db2.setPassword("123456");

    DbComparator comparator = new DbComparator();
    CompareResult result = comparator.compare(db1, db2);

    System.out.println("表差异:");
    result.getTableDiffs().forEach(System.out::println);

    System.out.println("列差异:");
    result.getColumnDiffs().forEach(System.out::println);
  }
}

